﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using BGCOP.CAVTOP.BUSINESSLAYER;

namespace ImportaExcelWindows
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ofd.ShowDialog();
            txtFileEmp.Text = ofd.FileName;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                OleDbConnection oConn = new OleDbConnection();
                OleDbCommand oCmd = new OleDbCommand();
                OleDbDataAdapter oDa = new OleDbDataAdapter();
                DataSet oDs = new DataSet();

                String strDestino = txtFileEmp.Text;
                oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

                oCmd.CommandText = @"select * from DATOS";
                oCmd.Connection = oConn;
                oDa.SelectCommand = oCmd;
                oDa.Fill(oDs);
                Empresas tmp = new Empresas();

                foreach (DataRow dtFila in oDs.Tables[0].Rows)
                {
                    textBox2.AppendText(dtFila[0].ToString() + " " + dtFila[1].ToString() +

                                        "\r\n");
                    var em = new TEmpresa();
                    em.nit = dtFila[1].ToString();
                    em.RazonSocial = dtFila[0].ToString();
                    em.telefonos = dtFila[3].ToString();
                    em.telContactoInterno = dtFila[5].ToString();
                    em.EmpleadoContacto = dtFila[4].ToString();
                    em.ObservacioneEmpresa = dtFila[7].ToString();
                    em.fechaUltimaGestion = DateTime.Now;
                    em.usuarioUltimaGestion = 1;
                    em.CargoContacto = dtFila[6].ToString();
                    tmp.CrearEmpresa(em, 1);

                    Application.DoEvents();
                }
                MessageBox.Show("Terminado");
            }
            catch (Exception ex)
            {
                textBox2.AppendText(ex.ToString() + "\r\n");
                Console.WriteLine(ex.Message);
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            ofd.ShowDialog();
            txtDivi.Text = ofd.FileName;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                OleDbConnection oConn = new OleDbConnection();
                OleDbCommand oCmd = new OleDbCommand();
                OleDbDataAdapter oDa = new OleDbDataAdapter();
                DataSet oDs = new DataSet();

                String strDestino = txtDivi.Text;
                oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

                oCmd.CommandText = @"select * from DATOS";
                oCmd.Connection = oConn;
                oDa.SelectCommand = oCmd;
                oDa.Fill(oDs);
                DivisionesEmpresa tmp = new DivisionesEmpresa();
                Empresas emp = new Empresas();
                foreach (DataRow dtFila in oDs.Tables[0].Rows)
                {
                    textBox2.AppendText(dtFila[0].ToString() + " " + dtFila[1].ToString() +

                                        "\r\n");
                    TEmpresa obj = emp.Consultar(dtFila[2].ToString());
                    tmp.AgregarDivision(new TDivisionEmpresa()
                                            {
                                                IdEmpresa = obj.idEmpresa,
                                                NombreDivision = dtFila[0].ToString(),
                                                piso = dtFila[1].ToString(),
                                                fechaUltimaGestion = DateTime.Now,
                                                usuarioUltimaGestion = 1
                                            });

                    Application.DoEvents();
                }
                MessageBox.Show("Terminado");
            }
            catch (Exception ex)
            {
                textBox2.AppendText(ex.ToString() + "\r\n");
                Console.WriteLine(ex.Message);
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            ofd.ShowDialog();
            txtEmple.Text = ofd.FileName;
        }

        private void button5_Click(object sender, EventArgs e)
        {
            OleDbConnection oConn = new OleDbConnection();
            OleDbCommand oCmd = new OleDbCommand();
            OleDbDataAdapter oDa = new OleDbDataAdapter();
            DataSet oDs = new DataSet();

            String strDestino = txtEmple.Text;
            oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

            oCmd.CommandText = @"select * from DATOS";
            oCmd.Connection = oConn;
            oDa.SelectCommand = oCmd;
            oDa.Fill(oDs);
            DivisionesEmpresa divi = new DivisionesEmpresa();
            Empresas emp = new Empresas();
            TiposIdentificacion ti = new TiposIdentificacion();
            Empleados empleadosDB = new Empleados();
            foreach (DataRow dtFila in oDs.Tables[0].Rows)
            {
                //obtener la empresa del empleado
                textBox2.AppendText(dtFila[0].ToString() + " " + dtFila[4].ToString() +

                                        "\r\n");
                var emp1 = emp.Consultar(dtFila[0].ToString());
                var tipodoc = ti.consulta(dtFila[2].ToString());
                var depen = divi.consulta(dtFila[1].ToString());
                empleadosDB.CrearEmpleado(dtFila[4].ToString(), dtFila[7].ToString(), emp1.RazonSocial, depen.NombreDivision, tipodoc.descripcionIdentificacion, dtFila[8].ToString(), dtFila[10].ToString(), DateTime.Now.ToString(), "0000000", 1);
                Application.DoEvents();
            }
            MessageBox.Show("Terminado");
        }

        private void button8_Click(object sender, EventArgs e)
        {
            ofd.ShowDialog();
            txtVEH.Text = ofd.FileName;
        }

        private void button7_Click(object sender, EventArgs e)
        {
            OleDbConnection oConn = new OleDbConnection();
            OleDbCommand oCmd = new OleDbCommand();
            OleDbDataAdapter oDa = new OleDbDataAdapter();
            DataSet oDs = new DataSet();

            String strDestino = txtVEH.Text;
            oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

            oCmd.CommandText = @"select * from DATOS";
            oCmd.Connection = oConn;
            oDa.SelectCommand = oCmd;
            oDa.Fill(oDs);
            Vehiculos ve = new Vehiculos();
            foreach (DataRow dtFila in oDs.Tables[0].Rows)
            {
                textBox2.AppendText(dtFila[0].ToString() + " " + dtFila[4].ToString() +

                                        "\r\n");
                ve.Agregar(dtFila[0].ToString(), dtFila[1].ToString(), dtFila[6].ToString(), dtFila[4].ToString(), DateTime.Now, 1);
                Application.DoEvents();
            }
            MessageBox.Show("Terminado");
        }

        private void button10_Click(object sender, EventArgs e)
        {
            ofd.ShowDialog();
            txtVHEEMP.Text = ofd.FileName;
        }

        private void button9_Click(object sender, EventArgs e)
        {
            OleDbConnection oConn = new OleDbConnection();
            OleDbCommand oCmd = new OleDbCommand();
            OleDbDataAdapter oDa = new OleDbDataAdapter();
            DataSet oDs = new DataSet();

            String strDestino = txtVHEEMP.Text;
            oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

            oCmd.CommandText = @"select * from DATOS";
            oCmd.Connection = oConn;
            oDa.SelectCommand = oCmd;
            oDa.Fill(oDs);
            Vehiculos ve = new Vehiculos();
            foreach (DataRow dtFila in oDs.Tables[0].Rows)
            {
                textBox2.AppendText(dtFila[0].ToString() + " " + dtFila[1].ToString() +

                                        "\r\n");
                ve.asignarVehiculoEmpleado(dtFila[0].ToString(), dtFila[1].ToString());
                Application.DoEvents();
            }
            MessageBox.Show("Terminado");
        }

        private void button12_Click(object sender, EventArgs e)
        {
            ofd.ShowDialog();
            txtTarje.Text = ofd.FileName;
        }

        private void button11_Click(object sender, EventArgs e)
        {
            OleDbConnection oConn = new OleDbConnection();
            OleDbCommand oCmd = new OleDbCommand();
            OleDbDataAdapter oDa = new OleDbDataAdapter();
            DataSet oDs = new DataSet();

            String strDestino = txtTarje.Text;
            oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

            oCmd.CommandText = @"select * from DATOS1";
            oCmd.Connection = oConn;
            oDa.SelectCommand = oCmd;
            oDa.Fill(oDs);
            Tarjetas tj = new Tarjetas();
            MessageBox.Show(oDs.Tables[0].Rows.Count.ToString());
            foreach (DataRow dtFila in oDs.Tables[0].Rows)
            {
                textBox2.AppendText(dtFila[0].ToString() + " " + dtFila[2].ToString() + "\r\n");
                tj.asignartarjetaempleado(dtFila[0].ToString(), Convert.ToInt32(dtFila[2].ToString()));
                
                Application.DoEvents();
            }
            MessageBox.Show("Terminado");
        }

        private void button14_Click(object sender, EventArgs e)
        {
            ofd.ShowDialog();
            txtVP.Text = ofd.FileName;
        }

        private void button13_Click(object sender, EventArgs e)
        {
            OleDbConnection oConn = new OleDbConnection();
            OleDbCommand oCmd = new OleDbCommand();
            OleDbDataAdapter oDa = new OleDbDataAdapter();
            DataSet oDs = new DataSet();

            String strDestino = txtVP.Text;
            oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

            oCmd.CommandText = @"select * from DATOS";
            oCmd.Connection = oConn;
            oDa.SelectCommand = oCmd;
            oDa.Fill(oDs);
            VehiculosParqueos vp = new VehiculosParqueos();
            Vehiculos ve = new Vehiculos();

            MessageBox.Show(oDs.Tables[0].Rows.Count.ToString());
            int xx = 0;
            string ant1 = "";
            string ant2 = "";
            foreach (DataRow dtFila in oDs.Tables[0].Rows)
            {
                textBox2.AppendText(dtFila[0].ToString() + " " + dtFila[2].ToString() + "\r\n");
                //verifica si existe
                var car = ve.consultarVehiculo(dtFila[0].ToString());
                if (car != null)
                {
                    if (dtFila[0].ToString() != ant1)
                    {
                        listBox1.Items.Add(dtFila[0].ToString());
                        ant1 = dtFila[0].ToString();
                        var uu = vp.consultarreg(Convert.ToInt32(dtFila[2].ToString()), car.idVehiculo);
                        if (uu == null)
                        {
                            //se crea
                            vp.Agregar(Convert.ToInt32(dtFila[2].ToString()), car.idVehiculo, DateTime.Now, 1);
                        }
                        else
                        {
                            //modifica
                            
                        }
                    }
                    vp.modificar(Convert.ToInt32(dtFila[2].ToString()), car.idVehiculo,
                                         Convert.ToInt32(dtFila[1].ToString()), true);
                    

                }
                else
                {
                    if (dtFila[0].ToString() != ant2)
                    {
                        listBox2.Items.Add(dtFila[0].ToString());
                        ant2 = dtFila[0].ToString();
                        xx++;

                    }
                }

                Application.DoEvents();
            }
            MessageBox.Show("Terminado :" + listBox1.Items.Count.ToString() + " vs " + listBox2.Items.Count.ToString());
        }

        private void button16_Click(object sender, EventArgs e)
        {
            ofd.ShowDialog();
            txtVISI.Text = ofd.FileName;
        }

        private void button15_Click(object sender, EventArgs e)
        {
            OleDbConnection oConn = new OleDbConnection();
            OleDbCommand oCmd = new OleDbCommand();
            OleDbDataAdapter oDa = new OleDbDataAdapter();
            DataSet oDs = new DataSet();

            String strDestino = txtVISI.Text;
            oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

            oCmd.CommandText = @"select * from DATA";
            oCmd.Connection = oConn;
            oDa.SelectCommand = oCmd;
            oDa.Fill(oDs);
            Visitantes vs = new Visitantes();
            TiposIdentificacion ti = new TiposIdentificacion();
            MessageBox.Show(oDs.Tables[0].Rows.Count.ToString());
            foreach (DataRow dtFila in oDs.Tables[0].Rows)
            {

                textBox2.AppendText(dtFila[0].ToString() + " " + dtFila[2].ToString() + "\r\n");
                 
                vs.crear(new TVisitante()
                             {
                                 empresa = dtFila[4].ToString(),
                                 idTipoIdentificacion = ti.consulta(dtFila[0].ToString()).idTipoIdentificacion,
                                 nombreVisitante = dtFila[2].ToString() + " " + dtFila[3].ToString(),
                                 identificacionVisitante = dtFila[1].ToString()
                             });

                Application.DoEvents();
            }
            MessageBox.Show("Terminado");
        }

        private void button18_Click(object sender, EventArgs e)
        {
            ofd.ShowDialog();
            txtTJ.Text = ofd.FileName;
        }

        private void button17_Click(object sender, EventArgs e)
        {
            OleDbConnection oConn = new OleDbConnection();
            OleDbCommand oCmd = new OleDbCommand();
            OleDbDataAdapter oDa = new OleDbDataAdapter();
            DataSet oDs = new DataSet();

            String strDestino = txtTJ.Text;
            oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

            oCmd.CommandText = @"select * from DATOS1";
            oCmd.Connection = oConn;
            oDa.SelectCommand = oCmd;
            oDa.Fill(oDs);
            Tarjetas tj = new Tarjetas();
            MessageBox.Show(oDs.Tables[0].Rows.Count.ToString());
            foreach (DataRow dtFila in oDs.Tables[0].Rows)
            {
                textBox2.AppendText(dtFila[0].ToString() + " " + dtFila[0].ToString() + "\r\n");
                
                //tj.asignartarjetaempleado(dtFila[0].ToString(), Convert.ToInt32(dtFila[2].ToString()));
                tj.crearTarjeta(dtFila[0].ToString());
                Application.DoEvents();
            }
            MessageBox.Show("Terminado");
        }

        private void button19_Click(object sender, EventArgs e)
        {
            Tarjetas tj = new Tarjetas();
            Empleados em = new Empleados();
            var xx = em.ListarTodos().Count;
            for (int i = 1; i <= 629; i++)
            {
                tj.asignartarjetaempleado(i, i);
            }
            MessageBox.Show("Terminado");
        }

        private void button21_Click(object sender, EventArgs e)
        {
            ofd.ShowDialog();
            txtPerBM.Text = ofd.FileName;
        }

        private void button20_Click(object sender, EventArgs e)
        {
            OleDbConnection oConn = new OleDbConnection();
            OleDbCommand oCmd = new OleDbCommand();
            OleDbDataAdapter oDa = new OleDbDataAdapter();
            DataSet oDs = new DataSet();

            String strDestino = txtPerBM.Text;
            oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

            oCmd.CommandText = @"select * from DATOS1";
            oCmd.Connection = oConn;
            oDa.SelectCommand = oCmd;
            oDa.Fill(oDs);
            Empleados em = new Empleados();
             
            foreach (DataRow dtFila in oDs.Tables[0].Rows)
            {
                textBox2.AppendText(dtFila[0].ToString() + " " + dtFila[0].ToString() + "\r\n");
                textBox1.Text = textBox1.Text + " identificacion ='" + dtFila[2].ToString() + "' or";
                //em.cambiarHorario(dtFila[2].ToString(), 2);
            }
            MessageBox.Show("Terminado");
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
    }
}